<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{
    var $_total = null;
    var $_pagination = null;
    var $tbl = null;
    var $_username = null;

    public function __construct(){
        ini_set("memory_limit","1024M");
    	parent::__construct();

        $mainframe = JFactory::getApplication();

        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');

        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);

        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
    }


    function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            $query = "SELECT count(*) FROM $this->tbl $where";
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }

        return $this->_total;
    }

    function getTotalFull($query){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            $this->_db->setQuery("SELECT COUNT(*) FROM($query) AS tbl");

            $this->_total = $this->_db->loadResult();
        }

        return $this->_total;
    }

    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(),
                                                $this->getState('limitstart'),
                                                $this->getState('limit') );
        }
        return $this->_pagination;
    }

    public function getSearch($filter){
        switch($filter['folder']){
            case 'inbox':
                return $this->getSearchInbox($filter);
            break;

            case 'sent':
                $tbl = 'MESE';
                $where = " WHERE MESE_USEM_CODE = '$this->_username'
                        AND (MESE_DELF IS NULL OR MESE_DELF = 0)
                        AND (MESE_DRAF IS NULL OR MESE_DRAF = 0)
                        AND (MESE_FOME_CODE IS NULL OR MESE_FOME_CODE = 0) ";
            break;

            /*case 'draft':
                $tbl = '';
                $where = " WHERE _USEM_CODE = '$this->_username'
                            AND _DRAF IS NOT NULL
                            AND (_FOME_CODE IS NULL OR _FOME_CODE = 0)";
            break;*/

            case 'trash':
                $tbl = 'MERE';
                $where = "  WHERE MERE_OCBA_CODE = '$this->_username'
                            AND MERE_DELF IS NOT NULL
                            AND (MERE_FOME_CODE IS NULL OR MERE_FOME_CODE = 0) ";
            break;

            case 'all':
                $where = $this->getFilter($filter, 'MERE');
                $qr = "SELECT   MERE_TITLE as F_TITLE,
                        MERE_DATE as F_DATE,
                        MERE_OCBA_CODE as F_OCBA_CODE,
                        MERE_USEM_CODE as F_USEM_CODE,
                        MERE_REPF as F_REPF,
                        MERE_DELF as F_DELF,
                        MERE_CHKF as F_CHKF,
                        MERE_FWD as F_FWD,
                        MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mere_',MERE_CODE) as F_CODE
                FROM mere
                WHERE MERE_OCBA_CODE = '$this->_username' $where";

                $where = $this->getFilter($filter, 'mese');
                $qr .= "UNION (
                    SELECT   MESE_TITLE as F_TITLE,
                            MESE_DATE as F_DATE,
                            MESE_OCBA_CODE as F_OCBA_CODE,
                            MESE_USEM_CODE as F_USEM_CODE,
                            MESE_REPF as F_REPF,
                            MESE_DELF as F_DELF,
                            MESE_CHKF as F_CHKF,
                            MESE_FWD as F_FWD,
                            MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('MESE_',MESE_CODE) as F_CODE
                    FROM mese
                    WHERE MESE_USEM_CODE = '$this->_username' AND (MESE_DRAF IS NULL OR MESE_DRAF =0 ) $where
                )
                ORDER BY F_DATE DESC ";
// echo $qr;
                // get phan trang
                $this->_db->setQuery($qr);
                $this->_total = count($this->_db->loadObjectList()); ;

                $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

                // get query
                $this->_db->setQuery($qr);

                return $this->_db->loadObjectList();
                /************************************** END *********/
            break;

            default :
                // inbox
                $where = '';
                if($filter){
                    if($filter['sender']){
                        $where .= " AND MERE_USEM_NAME LIKE '%$filter[sender]%'";
                    }
                    if($filter['title']){
                        $where .= " AND MERE_TITLE LIKE '%$filter[title]%'";
                    }
                    if($filter['id_mail_group']){
                        $where .= " AND MERE_OCBA_CODE REGEXP '[*]{1}.*($filter[id_mail_group])' ";
                    }
                    if($filter['name_mail_group']){
                        $where .= " AND MERE_OCBA_CODE LIKE '%$filter[name_mail_group]%(_);%' ";
                    }
                    if($filter['dateFrom']){
                        $where .= " AND MERE_DATE >= '$filter[dateFrom] 00:00:00' ";
                    }
                    if($filter['dateTo']){
                        $where .= " AND MERE_DATE <= '$filter[dateTo] 23:59:59'";
                    }
                    if($filter['attachment']){
                        $where .= " AND MERE_ATTA_CODE_LIST NOT IN ('', 'null', '[]', '|')";
                    }
                }
                $qr = "SELECT   MERE_TITLE as F_TITLE,
                        MERE_DATE as F_DATE,
                        MERE_OCBA_CODE as F_OCBA_CODE,
                        MERE_USEM_CODE as F_USEM_CODE,
                        MERE_REPF as F_REPF,
                        MERE_DELF as F_DELF,
                        MERE_CHKF as F_CHKF,
                        MERE_FWD as F_FWD,
                        MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mere_',MERE_CODE) as F_CODE
                FROM mere
                WHERE MERE_OCBA_CODE = '$this->_username'
                    AND (MERE_DELF IS NULL OR MERE_DELF = 0)
                    AND MERE_FOME_CODE = '$filter[folder]' $where";

                // sentbox
                 $where = '';
                if($filter){
                    if($filter['recipient']){
                        $where .= " AND MESE_OCBA_CODE LIKE '%$filter[recipient]%'";
                    }
                    if($filter['title']){
                        $where .= " AND MESE_TITLE LIKE '%$filter[title]%'";
                    }
                    if($filter['id_mail_group']){
                        $where .= " AND MESE_OCBA_CODE REGEXP '[*]{1}.*($filter[id_mail_group])' ";
                    }
                    if($filter['name_mail_group']){
                        $where .= " AND MESE_OCBA_CODE LIKE '%$filter[name_mail_group]%(_);%' ";
                    }
                    if($filter['dateFrom']){
                        $where .= " AND MESE_DATE >= '$filter[dateFrom] 00:00:00' ";
                    }
                    if($filter['dateTo']){
                        $where .= " AND MESE_DATE <= '$filter[dateTo] 23:59:59'";
                    }
                    if($filter['attachment']){
                        $where .= " AND MESE_ATTA_CODE_LIST NOT IN ('', 'null', '[]', '|')";
                    }
                }

                $qr .= "UNION (
                    SELECT   MESE_TITLE as F_TITLE,
                            MESE_DATE as F_DATE,
                            MESE_OCBA_CODE as F_OCBA_CODE,
                            MESE_USEM_CODE as F_USEM_CODE,
                            MESE_REPF as F_REPF,
                            MESE_DELF as F_DELF,
                            MESE_CHKF as F_CHKF,
                            MESE_FWD as F_FWD,
                            MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('MESE_',MESE_CODE) as F_CODE
                    FROM mese
                    WHERE MESE_USEM_CODE = '$this->_username'
                        AND (MESE_DELF IS NULL OR MESE_DELF = 0)
                        AND MESE_FOME_CODE = '$filter[folder]' $where
                    )

                    ORDER BY F_DATE DESC ";

                // get phan trang
                $this->_db->setQuery($qr);
                $this->_total = count($this->_db->loadObjectList()); ;

                $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

                // get query
                $this->_db->setQuery($qr);

                return $this->_db->loadObjectList();
                /************************************** END *********/
        }

        $where .= $this->getFilter($filter, $tbl);
        // get total number record
        $this->tbl = $tbl;
        $this->_total = $this->getTotal($where);

        $qr = "SELECT  ".$tbl."_TITLE as F_TITLE,
                        ".$tbl."_DATE as F_DATE,
                        ".$tbl."_OCBA_CODE as F_OCBA_CODE,
                        ".$tbl."_USEM_CODE as F_USEM_CODE,
                        ".$tbl."_REPF as F_REPF,
                        ".$tbl."_DELF as F_DELF,
                        ".$tbl."_CHKF as F_CHKF,
                        ".$tbl."_FWD as F_FWD,
                        ".$tbl."_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('".strtolower($tbl)."_',".$tbl."_CODE) as F_CODE
                FROM ".strtolower($tbl)." $where ORDER BY ".$tbl."_CODE DESC
                LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();
       return $result;

    }

    /** khong lay message trong Draft */
    /*
        Tim sender trong table "MERE - nguoi nhan"
        Tim recipient trong table "MESE - nguoi gui"
        
        Chi tim SENDER khi 
            1. Sender duoc chon
            2. Ca sender va recipient deu khong duoc chon (tim theo gia tri khac)
     */
    public function getSearchAll($filter){

        $allowSender = $filter['sender'] || ($filter['recipient'] == '' && $filter['sender'] == '');
        $allowRecipient = $filter['recipient'] || ($filter['recipient'] == '' && $filter['sender'] == '');
        
        if( $allowSender ){

            // xu ly search theo dieu kien
            $where = $this->getFilterShort($filter, 'MERE');
            if($filter['sender']){
                $where .= " AND MERE_USEM_NAME LIKE '%$filter[sender]%'";
            }

            $qr = "SELECT   MERE_TITLE      as F_TITLE,
                            MERE_DATE       as F_DATE,
                            MERE_OCBA_CODE  as F_OCBA_CODE,
                            MERE_USEM_CODE  as F_USEM_CODE,
                            MERE_REPF       as F_REPF,
                            MERE_DELF       as F_DELF,
                            MERE_CHKF       as F_CHKF,
                            MERE_FWD        as F_FWD,
                            MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('mere_', MERE_CODE) as F_CODE
                    FROM mere 
                    WHERE MERE_OCBA_CODE = '$this->_username'
                        $where ";
        }

        if($allowSender && $allowRecipient){
            $qr .= " UNION ";
        }

        if($allowRecipient){

            // xu ly search theo dieu kien
            $where = $this->getFilterShort($filter, 'MESE');
            if($filter['recipient']){
                $where .= " AND MESE_OCBA_CODE LIKE '%$filter[recipient]%'";
            }

            $qr .= "(SELECT   MESE_TITLE      as F_TITLE,
                            MESE_DATE       as F_DATE,
                            MESE_OCBA_CODE  as F_OCBA_CODE,
                            MESE_USEM_CODE  as F_USEM_CODE,
                            MESE_REPF       as F_REPF,
                            MESE_DELF       as F_DELF,
                            MESE_CHKF       as F_CHKF,
                            MESE_FWD        as F_FWD,
                            MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('mese_', MESE_CODE) as F_CODE
                    FROM mese 
                    WHERE  MESE_USEM_CODE = '$this->_username'
                            AND (MESE_DRAF IS NULL OR MESE_DRAF = 0)
                        $where )";
        }

        // get total
        $this->_total = $this->getTotalFull($qr);
        
        $qr .= "ORDER BY F_CODE DESC
            LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();
       return $result;

    }

    public function getSearchInbox($filter){

        // xu ly search theo dieu kien
        $where = $this->getFilterShort($filter, 'MERE');
        if($filter['sender']){
            $where .= " AND MERE_USEM_NAME LIKE '%$filter[sender]%'";
        }

        $qr = "SELECT   MERE_TITLE      as F_TITLE,
                        MERE_DATE       as F_DATE,
                        MERE_OCBA_CODE  as F_OCBA_CODE,
                        MERE_USEM_CODE  as F_USEM_CODE,
                        MERE_REPF       as F_REPF,
                        MERE_DELF       as F_DELF,
                        MERE_CHKF       as F_CHKF,
                        MERE_FWD        as F_FWD,
                        MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mere_', MERE_CODE) as F_CODE
                FROM mere 
                WHERE MERE_OCBA_CODE = '$this->_username'
                    AND (MERE_DELF IS NULL OR MERE_DELF = 0)
                    AND (MERE_FOME_CODE IS NULL OR MERE_FOME_CODE = 0) 
                    $where ";

            // get total
            $this->_total = $this->getTotalFull($qr);
            
            $qr .= "ORDER BY MERE_CODE DESC
                LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();
       return $result;
    }

    public function getSearchSent($filter){

        // xu ly search theo dieu kien
        $where = $this->getFilterShort($filter, 'MESE');
        if($filter['recipient']){
            $where .= " AND MESE_OCBA_CODE LIKE '%$filter[recipient]%'";
        }

        $qr = "SELECT   MESE_TITLE      as F_TITLE,
                        MESE_DATE       as F_DATE,
                        MESE_OCBA_CODE  as F_OCBA_CODE,
                        MESE_USEM_CODE  as F_USEM_CODE,
                        MESE_REPF       as F_REPF,
                        MESE_DELF       as F_DELF,
                        MESE_CHKF       as F_CHKF,
                        MESE_FWD        as F_FWD,
                        MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mese_', MESE_CODE) as F_CODE
                FROM mese 
                WHERE  MESE_USEM_CODE = '$this->_username'
                        AND (MESE_DELF IS NULL OR MESE_DELF = 0)
                        AND (MESE_DRAF IS NULL OR MESE_DRAF = 0)
                        AND (MESE_FOME_CODE IS NULL OR MESE_FOME_CODE = 0) 
                    $where ";
        
        // get total
        $this->_total = $this->getTotalFull($qr);

        // tiep tuc truy van
        $qr .= "ORDER BY MESE_CODE DESC
                LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();
       return $result;
    }

    /*
        Tim sender trong table "MERE - nguoi nhan"
        Tim recipient trong table "MESE - nguoi gui"
        
        Chi tim SENDER khi 
            1. Sender duoc chon
            2. Ca sender va recipient deu khong duoc chon (tim theo gia tri khac)
     */
    public function getSearchFolder($filter){

        $allowSender = $filter['sender'] || ($filter['recipient'] == '' && $filter['sender'] == '');
        $allowRecipient = $filter['recipient'] || ($filter['recipient'] == '' && $filter['sender'] == '');
        
        if( $allowSender ){
            // xu ly search theo dieu kien
            $where = $this->getFilterShort($filter, 'MERE');
            if($filter['sender']){
                $where .= " AND MERE_USEM_NAME LIKE '%$filter[sender]%'";
            }

            $qr = "SELECT   MERE_TITLE      as F_TITLE,
                            MERE_DATE       as F_DATE,
                            MERE_OCBA_CODE  as F_OCBA_CODE,
                            MERE_USEM_CODE  as F_USEM_CODE,
                            MERE_REPF       as F_REPF,
                            MERE_DELF       as F_DELF,
                            MERE_CHKF       as F_CHKF,
                            MERE_FWD        as F_FWD,
                            MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('mere_', MERE_CODE) as F_CODE
                    FROM mere 
                    WHERE MERE_OCBA_CODE = '$this->_username'
                        AND (MERE_DELF IS NULL OR MERE_DELF = 0)
                        AND (MERE_FOME_CODE = '$filter[folder]') 
                        $where ";
        }

        if($allowSender && $allowRecipient){
            $qr .= " UNION ";
        }

        if($allowRecipient){

            // xu ly search theo dieu kien
            $where = $this->getFilterShort($filter, 'MESE');
            if($filter['recipient']){
                $where .= " AND MESE_OCBA_CODE LIKE '%$filter[recipient]%'";
            }

            $qr .= "(SELECT   MESE_TITLE      as F_TITLE,
                            MESE_DATE       as F_DATE,
                            MESE_OCBA_CODE  as F_OCBA_CODE,
                            MESE_USEM_CODE  as F_USEM_CODE,
                            MESE_REPF       as F_REPF,
                            MESE_DELF       as F_DELF,
                            MESE_CHKF       as F_CHKF,
                            MESE_FWD        as F_FWD,
                            MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('mese_', MESE_CODE) as F_CODE
                    FROM mese 
                    WHERE  MESE_USEM_CODE = '$this->_username'
                            AND (MESE_DELF IS NULL OR MESE_DELF = 0)
                            AND (MESE_DRAF IS NULL OR MESE_DRAF = 0)
                            AND (MESE_FOME_CODE = '$filter[folder]') 
                        $where )";
        }

        // get total
        $this->_total = $this->getTotalFull($qr);
        
        $qr .= "ORDER BY F_CODE DESC
            LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();
       return $result;

    }

    /*
        Tim sender trong table "MERE - nguoi nhan"
        Tim recipient trong table "MESE - nguoi gui"
        
        Chi tim SENDER khi 
            1. Sender duoc chon
            2. Ca sender va recipient deu khong duoc chon (tim theo gia tri khac)
     */
    public function getSearchTrash($filter){

        $allowSender = $filter['sender'] || ($filter['recipient'] == '' && $filter['sender'] == '');
        $allowRecipient = $filter['recipient'] || ($filter['recipient'] == '' && $filter['sender'] == '');
        
        if( $allowSender ){

            // xu ly search theo dieu kien
            $where = $this->getFilterShort($filter, 'MERE');
            if($filter['sender']){
                $where .= " AND MERE_USEM_NAME LIKE '%$filter[sender]%'";
            }

            $qr = "SELECT   MERE_TITLE      as F_TITLE,
                            MERE_DATE       as F_DATE,
                            MERE_OCBA_CODE  as F_OCBA_CODE,
                            MERE_USEM_CODE  as F_USEM_CODE,
                            MERE_REPF       as F_REPF,
                            MERE_DELF       as F_DELF,
                            MERE_CHKF       as F_CHKF,
                            MERE_FWD        as F_FWD,
                            MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('mere_', MERE_CODE) as F_CODE
                    FROM mere 
                    WHERE MERE_OCBA_CODE = '$this->_username'
                        AND (MERE_DELF IS NOT NULL OR MERE_DELF != 0)
                        AND (MERE_FOME_CODE IS NULL OR MERE_FOME_CODE = 0) 
                        $where ";
        }

        if($allowSender && $allowRecipient){
            $qr .= " UNION ";
        }

        if($allowRecipient){

            // xu ly search theo dieu kien
            $where = $this->getFilterShort($filter, 'MESE');
            if($filter['recipient']){
                $where .= " AND MESE_OCBA_CODE LIKE '%$filter[recipient]%'";
            }

            $qr .= "(SELECT   MESE_TITLE      as F_TITLE,
                            MESE_DATE       as F_DATE,
                            MESE_OCBA_CODE  as F_OCBA_CODE,
                            MESE_USEM_CODE  as F_USEM_CODE,
                            MESE_REPF       as F_REPF,
                            MESE_DELF       as F_DELF,
                            MESE_CHKF       as F_CHKF,
                            MESE_FWD        as F_FWD,
                            MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                            CONCAT('mese_', MESE_CODE) as F_CODE
                    FROM mese 
                    WHERE  MESE_USEM_CODE = '$this->_username'
                            AND (MESE_DELF IS NOT NULL OR MESE_DELF != 0)
                            AND (MESE_DRAF IS NULL OR MESE_DRAF = 0)
                            AND (MESE_FOME_CODE IS NULL OR MESE_FOME_CODE = 0) 
                        $where )";
        }

        // get total
        $this->_total = $this->getTotalFull($qr);
        




        $qr .= "ORDER BY F_CODE DESC
            LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();
       return $result;

    }

    public function getFilterShort($filter, $type){
        $where = '';
       if($filter){
            if($filter['title']){
                $where .= " AND ".$type."_TITLE LIKE '%$filter[title]%'";
            }
            if($filter['id_mail_group']){
                $where .= " AND ".$type."_OCBA_CODE LIKE '%$filter[id_mail_group]%' ";
            }
            if($filter['name_mail_group']){
                $where .= " AND ".$type."_OCBA_CODE LIKE '%$filter[name_mail_group]%' ";
            }
            if($filter['dateFrom']){
                $where .= " AND ".$type."_DATE >= '$filter[dateFrom] 00:00:00' ";
            }
            if($filter['dateTo']){
                $where .= " AND ".$type."_DATE <= '$filter[dateTo] 23:59:59'";
            }
            if($filter['attachment']){
                $where .= " AND ".$type."_ATTA_CODE_LIST NOT IN ('', 'null', '[]', '|')";
            }
       }
       return $where;
    }

    public function getFilter($filter, $type){
    	// search theo filter
        $where = '';
       if($filter){
            if($filter['sender']){
                $where .= " AND ".$type."_USEM_NAME LIKE '%$filter[sender]%'";
            }
            if($filter['recipient']){
                // neu search o muc sentbox thi kiem tra nguoi nhan la OCBA_CODE
                /*if($filter['folder'] == 'sent'){
                    $where .= " AND ".$type."_OCBA_CODE LIKE '%$filter[recipient]%'";
                }else{
                    // con lai kiem tra OCBA_NAME
                    $where .= " AND ".$type."_OCBA_NAME LIKE '%$filter[recipient]%'";
                }*/
                $where .= " AND ({$type}_OCBA_NAME LIKE '%$filter[recipient]%'
                                    OR {$type}_OCBA_CODE LIKE '%$filter[recipient]%')";
                
            }
            if($filter['title']){
                $where .= " AND ".$type."_TITLE LIKE '%$filter[title]%'";
            }
            if($filter['id_mail_group']){
                $where .= " AND ".$type."_OCBA_CODE REGEXP '[*]{1}.*($filter[id_mail_group])' ";
            }
            if($filter['name_mail_group']){
                $where .= " AND ".$type."_OCBA_CODE LIKE '%$filter[name_mail_group]%(_);%' ";
            }
            if($filter['dateFrom']){
                $where .= " AND ".$type."_DATE >= '$filter[dateFrom] 00:00:00' ";
            }
            if($filter['dateTo']){
                $where .= " AND ".$type."_DATE <= '$filter[dateTo] 23:59:59'";
            }
            if($filter['attachment']){
                $where .= " AND ".$type."_ATTA_CODE_LIST NOT IN ('', 'null', '[]', '|')";
            }
       }
       return $where;
    }

	/**
	 * Get all message inbox
	 * @return
	 */
	function getInbox($title = null, $filter = array()){
	   // set type
       $this->tbl = 'mere';

	   $where = " WHERE MERE_OCBA_CODE = '$this->_username'
                    AND MERE_DELF IS NULL
                    AND (MERE_FOME_CODE IS NULL OR MERE_FOME_CODE = 0) ";

       // search theo title
       if($title){
            $where .= "AND MERE_TITLE LIKE '%$title%'";
       }

       // get total number record
       $this->_total = $this->getTotal($where);

       $qr = "SELECT MERE_TITLE as F_TITLE,
                    MERE_DATE as F_DATE,
                    MERE_OCBA_CODE as F_OCBA_CODE,
                    MERE_USEM_CODE as F_USEM_CODE,
                    MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                    MERE_REPF as F_REPF,
                    MERE_DELF as F_DELF,
                    MERE_CHKF as F_CHKF,
                    MERE_FWD as F_FWD,
                    CONCAT('MERE_',MERE_CODE) as F_CODE,
                    name
                FROM $this->tbl JOIN #__users ON username = MERE_USEM_CODE
                $where ORDER BY MERE_CODE DESC";
       /*$qr = "SELECT m.*, u.name FROM $this->tbl AS m
                JOIN kc2vr_users AS u ON u.username = m.MERE_USEM_CODE $where";*/

       // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();

       return $result;
	}

    /**
	 * Get all message draft
	 * @return
	 */
	function getTrash($title = null, $filter = array()){
       /*
        $where = " WHERE MERE_OCBA_CODE = '$this->_username'
                    AND MERE_DELF IS NOT NULL
                    AND (MERE_FOME_CODE IS NULL OR MERE_FOME_CODE = 0) ";

       // search theo title
       if($title){
            $where .= "AND MERE_TITLE LIKE '%a%'";
       }

       // get total number record
       $this->tbl = 'mere';
       $this->_total = $this->getTotal($where);
       */

       $where_mere = "WHERE MERE_OCBA_CODE = '$this->_username'
                        AND MERE_DELF IS NOT NULL
                        AND (MERE_FOME_CODE IS NULL OR MERE_FOME_CODE = 0)
                        AND MERE_TITLE LIKE '%$title%' ";

       $where_mese = "WHERE MESE_USEM_CODE = '$this->_username'
                        AND MESE_DELF IS NOT NULL
                        AND (MESE_FOME_CODE IS NULL OR MESE_FOME_CODE = 0)
                        AND MESE_TITLE LIKE '%$title%' ";

       $qr = "SELECT   MERE_TITLE as F_TITLE,
                        MERE_DATE as F_DATE,
                        MERE_OCBA_CODE as F_OCBA_CODE,
                        MERE_USEM_CODE as F_USEM_CODE,
                        MERE_REPF as F_REPF,
                        MERE_DELF as F_DELF,
                        MERE_CHKF as F_CHKF,
                        MERE_FWD as F_FWD,
                        MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mere_',MERE_CODE) as F_CODE
                FROM mere $where_mere

            UNION (
                SELECT   MESE_TITLE as F_TITLE,
                        MESE_DATE as F_DATE,
                        MESE_OCBA_CODE as F_OCBA_CODE,
                        MESE_USEM_CODE as F_USEM_CODE,
                        MESE_REPF as F_REPF,
                        MESE_DELF as F_DELF,
                        MESE_CHKF as F_CHKF,
                        MESE_FWD as F_FWD,
                        MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mese_',MESE_CODE) as F_CODE
                FROM mese $where_mese
            )
                ORDER BY F_DATE DESC";

       // get total number record
       $this->_total = $this->getTotalFull($qr);

        // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();

       return $result;
	}

    /**
	 * Get all message folder
	 * @return
	 */
	function getFolder($idFolder, $title = null, $filter = array()){
	   $qr = "SELECT   MERE_TITLE as F_TITLE,
                        MERE_DATE as F_DATE,
                        MERE_OCBA_CODE as F_OCBA_CODE,
                        MERE_USEM_CODE as F_USEM_CODE,
                        MERE_REPF as F_REPF,
                        MERE_DELF as F_DELF,
                        MERE_CHKF as F_CHKF,
                        MERE_FWD as F_FWD,
                        MERE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mere_',MERE_CODE) as F_CODE
                FROM mere
                WHERE MERE_OCBA_CODE = '$this->_username'
                    AND (MERE_DELF IS NULL OR MERE_DELF = 0)
                    AND MERE_FOME_CODE = '$idFolder'
                    AND MERE_TITLE LIKE '%$title%'
            UNION (
                SELECT   MESE_TITLE as F_TITLE,
                        MESE_DATE as F_DATE,
                        MESE_OCBA_CODE as F_OCBA_CODE,
                        MESE_USEM_CODE as F_USEM_CODE,
                        MESE_REPF as F_REPF,
                        MESE_DELF as F_DELF,
                        MESE_CHKF as F_CHKF,
                        MESE_FWD as F_FWD,
                        MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                        CONCAT('mese_',MESE_CODE) as F_CODE
                FROM mese
                WHERE MESE_USEM_CODE = '$this->_username'
                    AND (MESE_DELF IS NULL OR MESE_DELF = 0)
                    AND MESE_FOME_CODE = '$idFolder'
                    AND MESE_TITLE LIKE '%$title%' )
                ORDER BY F_DATE DESC";

        // get total number record
       $this->_total = $this->getTotalFull($qr);

        // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}

    /**
	 * Get all message draft
	 * @return
	 */
	function getDraft( $title = null, $filter = array()){
	   $where = " WHERE MESE_USEM_CODE = '$this->_username'
                    AND MESE_DRAF IS NOT NULL
                    AND (MESE_FOME_CODE IS NULL OR MESE_FOME_CODE = 0) ";

       // search theo title
       if($title){
            $where .= "AND MESE_TITLE LIKE '%$title%'";
       }

       // get total number record
       $this->tbl = 'mese';
       $this->_total = $this->getTotal($where);

	   $qr = "SELECT MESE_TITLE as F_TITLE,
                    MESE_DATE as F_DATE,
                    MESE_OCBA_CODE as F_OCBA_CODE,
                    MESE_USEM_CODE as F_USEM_CODE,
                    MESE_REPF as F_REPF,
                    MESE_DELF as F_DELF,
                    MESE_CHKF as F_CHKF,
                    MESE_FWD as F_FWD,
                    MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                    CONCAT('MESE_',MESE_CODE) as F_CODE
                FROM $this->tbl $where ORDER BY MESE_CODE DESC";

        // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}

    /**
	 * Get all message sent
	 * @return
	 */
	function getSent( $title = null, $filter = array()){
	   $where = " WHERE MESE_USEM_CODE = '$this->_username'
                    AND MESE_DELF IS NULL
                    AND (MESE_DRAF IS NULL OR MESE_DRAF =0 )
                    AND (MESE_FOME_CODE IS NULL OR MESE_FOME_CODE = 0) ";

       // search theo title
       if($title){
            $where .= "AND MESE_TITLE LIKE '%$title%'";
       }

       // get total number record
       $this->tbl = 'mese';
       $this->_total = $this->getTotal($where);

        $qr = "SELECT MESE_TITLE as F_TITLE,
                    MESE_DATE as F_DATE,
                    MESE_OCBA_CODE as F_OCBA_CODE,
                    MESE_USEM_CODE as F_USEM_CODE,
                    MESE_REPF as F_REPF,
                    MESE_DELF as F_DELF,
                    MESE_CHKF as F_CHKF,
                    MESE_FWD as F_FWD,
                    MESE_ATTA_CODE_LIST as F_ATTA_LIST,
                    CONCAT('MESE_',MESE_CODE) as F_CODE
                FROM $this->tbl $where ORDER BY MESE_CODE DESC";

        // pagination
       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

       $this->_db->setQuery($qr);
       $result = $this->_db->loadObjectList();
       return $result;
	}

    public function getDetailMessage($id, $type = 'MERE', $draft = false){
        $table = strtolower($type);
        $type = strtoupper($type);

    	$qr = "SELECT * FROM $table ";

        if(JRequest::getVar('cate') == 'folder'){
            $qr .= "JOIN fome ON {$type}_FOME_CODE = FOME_CODE ";
        }

        //$qr .= "OIN #__users ON username = {$type}_OCBA_CODE ";

        $qr .= "WHERE {$type}_CODE = '$id'
                    AND ( {$type}_USEM_CODE = '$this->_username' OR {$type}_OCBA_CODE = '$this->_username')";
        /*$qr = "SELECT m.*, u.name FROM $table AS m
                JOIN kc2vr_users AS u ON u.username = m.{$type}_USEM_CODE
                WHERE {$type}_CODE = '$id' ";*/
        // khong bao gio dung toi 20-12-2013
        if(0){
            $qr .= " AND {$type}_DELF IS NULL";
        }

        $this->_db->setQuery($qr);
       $result = $this->_db->loadObject();

       return $result;
    }

    public function setChecked($id){
   	    $qr = "UPDATE mere SET MERE_CHKF = 1 WHERE MERE_CODE = '$id'";

       $this->_db->setQuery($qr);
       return $this->_db->query();
    }

    public function setReply($id, $tbl){
   	    $qr = "UPDATE $tbl SET ".strtoupper($tbl)."_REPF = 1 WHERE ".strtoupper($tbl)."_CODE = '$id'";
    // echo $qr;

       $this->_db->setQuery($qr);
       return $this->_db->query();
    }

    public function setForward($id, $tbl){
   	    $qr = "UPDATE $tbl SET ".strtoupper($tbl)."_FWD = 1 WHERE ".strtoupper($tbl)."_CODE = '$id'";

       $this->_db->setQuery($qr);
       return $this->_db->query();
    }

    /**
     * bgModelbg::addMessageRecipient()
     * Sent message to recipients
     * @param mixed $data
     * @param mixed $recipients
     * @return
     */
    public function addMessageRecipient($data, $recipients, $attachments_sure){

        $attachments_sure = $attachments_sure ? implode('|', $attachments_sure) : null;

        $data['recipients'] = $data['OCBA_CODE_LIST'];

    	$recipient = explode(';', $data['OCBA_CODE_LIST']);
        foreach($recipient as $re){
            if($re){
                // backup $re, so check is group
                $group = $re;

                $ocbName = substr($re, 0, strripos($re,'(', 1));
                $re = substr($re, strripos($re,'(' ,1));
                $re = str_replace(')', '', $re);
                $re = str_replace('(', '', $re);
                //$data['OCBA_CODE_LIST'] = str_replace('(', '', $data['OCBA_CODE_LIST']);

                if(substr($group, 0, 1) == '*'){ // group mail
                    $userType = $this->getMAGR_USERTYPE($re);

                    switch($userType){
                        case 0:
                        case 3:
                        case 4:
                            // user system, center, club
                           $listUser = $this->getUserSystem($re);
                        break;

                        case 1:// coacher
                        break;

                        case 2:// out user
                        break;
                    }
                    // add list user in mailgroup to value insert
                    foreach($listUser as $u){
                        // duyet dinh kem cua user
                        //foreach($recipients[$u['username']] as $att){

                        //}
                        if(!$recipients[$u['username']]){
                            $recipients[$u['username']] = array();
                        }

                        $data['OCBA_CODE_LIST'] = $u['username'];
                        $data['OCBA_NAME'] = $u['name'];
                        $data['MESE_ATTA_CODE_LIST'] = implode('|', array_unique($recipients[$u['username']])); // gan tap dinh kem
                        $data['MESE_ATTA_CODE_LIST'] .= '|'.$attachments_sure; // gan dinh kem mac dinh cho tat ca


                        // gan file dinh kem chi dinh va file dinh kem mac dinh
                        /* edited 8/11 - loi khong gui duoc message mail group
                         if(!empty($attachments_sure)){
                            $data = array_merge($data, $attachments_sure);
                        }*/
                        $str[$data['OCBA_CODE_LIST']] = "('".implode("','", $data)."')";
                    }

                }else{ // list user mail
                    $data['OCBA_CODE_LIST'] = $re;
                    $data['OCBA_NAME'] = $ocbName;

                    // gan tap dinh kem
                    if($recipients[$re]){
                        $data['MESE_ATTA_CODE_LIST'] = implode('|', array_unique($recipients[$re]));
                    }else{
                        $data['MESE_ATTA_CODE_LIST'] = null;
                    }
                    $data['MESE_ATTA_CODE_LIST'] .= '|'.$attachments_sure; // gan dinh kem mac dinh cho tat ca

                    $str[$data['OCBA_CODE_LIST']] = "('".implode("','", $data)."')";
                }

            }
        }

        //$str = implode(',', $str);

        foreach ($str as $value) {
            $query = "INSERT INTO mere (MERE_USEM_CODE,
                                        MERE_USEM_NAME,
                                        MERE_TITLE,
                                        MERE_BODY,
                                        MERE_DATE,
                                        MERE_OCBA_CODE,
                                        MESE_CODE,
                                        MERE_OCBA_CODE_LIST,
                                        MERE_OCBA_NAME,
                                        MERE_ATTA_CODE_LIST)
                                        VALUES $value";

            $this->_db->setQuery($query);
            $this->_db->query();
        }

        return;

    }

    /**
     * bgModelbg::addMessageSend()
     * Sent message to sent
     * @param mixed $data
     * @param mixed $attachments_encode
     * @return
     */
    public function addMessageSend($data, $attachments_encode){
        $data['MESE_CHKF'] = 1;
        $str = implode("','", $data);
        $query = "INSERT INTO mese (MESE_USEM_CODE,MESE_USEM_NAME,MESE_TITLE,MESE_BODY,MESE_DATE,MESE_OCBA_CODE,MESE_CHKF,MESE_ATTA_CODE_LIST)
                                    VALUES ('$str','$attachments_encode')";

        $this->_db->setQuery($query);
        $this->_db->query();
        return $this->_db->insertid();
    }

    /**
     * bgModelbg::addMessageDraft()
     * Save draft message
     * @param mixed $data
     * @param mixed $attachments_encode
     * @return
     */
    public function addMessageDraft($data, $attachments_encode){
        $data['MESE_DRAF'] = 1;
        $data['MESE_CHKF'] = 1;
        $str = implode("','", $data);
        $query = "INSERT INTO mese (MESE_USEM_CODE,
                                    MESE_USEM_NAME,
                                    MESE_TITLE,
                                    MESE_BODY,
                                    MESE_DATE,
                                    MESE_OCBA_CODE,
                                    MESE_DRAF,
                                    MESE_CHKF,
                                    MESE_ATTA_CODE_LIST)
                                    VALUES ('$str','$attachments_encode')";

        $this->_db->setQuery($query);
        return  $this->_db->query();
    }

    /**
     * bgModelbg::getMAGR_USERTYPE()
     * Get usertype of mail group
     * @param mixed $id
     * @return
     */
    public function getMAGR_USERTYPE($id){
    	$query = "SELECT MAGR_USERTYPE FROM magr WHERE MAGR_CODE = $id";
        $this->_db->setQuery($query);
        return $this->_db->loadResult();
    }

    /**
     * bgModelbg::getUserSystem()
     * Get all user of mail group
     * @param mixed $idGroupMail
     * @return
     */
    public function getUserSystem($idGroupMail){
    	$query =   "SELECT username FROM kc2vr_users
                        WHERE id IN
                            (SELECT DISTINCT DEUM_USEM_COCH_CODE FROM deum
                                WHERE DEUM_MAGR_CODE = $idGroupMail)
                            AND sendEmail = 1";
        $this->_db->setQuery($query);

        return $this->_db->loadAssocList();
    }

    /**
     * bgModelbg::checkFlag()
     * If mess is checked, check forward and reply
     * Else return unread
     * @param mixed $mes
     * @param mixed $type
     * @return
     */
    public function checkFlag($mes, $type = 'MERE'){
        $mes = (array)$mes;
        $result = array();
        if($mes[$type.'_CHKF'] != 1){
            $result[] = '未読';
        }

        if($mes[$type.'_REPF']){
            $result[] = '返信';
        }
        if($mes[$type.'_FWD']){
            $result[]= '転送';
        }

        return implode(',', $result);
    }

    /**
     * bgModelbg::deleteMessage()
     * Update flag delete to 1
     * @param mixed $ids
     * @param mixed $type
     * @return
     */
    public function deleteMessage($id, $type){
        $tbl = strtolower($type);
        $qr = "UPDATE $tbl SET {$type}_DELF = 1, {$type}_FOME_CODE = 0 WHERE {$type}_CODE = $id ";

        // chi xoa message o inbox
        if($type == 'MERE'){
            $qr .= "AND MERE_OCBA_CODE = '$this->_username'";
        }
        /*
        if($type == 'MESE'){
            $qr .= "AND MESE_USEM_CODE = '$this->_username'";
        }
           echo $qr;*/
        $this->_db->setQuery($qr);
        return $this->_db->query();
    }


    /**
     * bgModelbg::deleteMessageForever()
     * Forever =)) delete messages from database
     * @param mixed $ids
     * @param mixed $type
     * @return
     */
    public function deleteMessageForever($ids, $type){
        $tbl = strtolower($type);
        $qr = "DELETE FROM $tbl WHERE {$type}_CODE IN($ids)
                AND ({$type}_USEM_CODE = '$this->_username' OR {$type}_OCBA_CODE = '$this->_username')";
    	$this->_db->setQuery($qr);

        return $this->_db->query();
    }

    public function getUnread($id_sent){
    	$query =   "SELECT MERE_OCBA_CODE FROM mere
                        WHERE MESE_CODE = '$id_sent' AND MERE_CHKF IS NULL AND MERE_USEM_CODE = '$this->_username'";

        $this->_db->setQuery($query);

        $result = $this->_db->loadObjectList();
        if(!$result){
            $result = array();
        }
        return $result;
    }

    public function getFolders(){

    	$query = "SELECT * FROM fome WHERE FOME_USEM_CODE = '$this->_username'";
        $this->_db->setQuery($query);

        return $this->_db->loadObjectList();
    }

    public function setFolder($id, $tbl, $idFolder){
        $type = strtoupper($tbl);
        $tbl = strtolower($tbl);

        $qr = "UPDATE $tbl SET {$type}_FOME_CODE = $idFolder, {$type}_DELF = 0
                WHERE {$type}_CODE = $id
                    AND ( {$type}_USEM_CODE = '$this->_username'
                    OR {$type}_OCBA_CODE = '$this->_username' )";

        $this->_db->setQuery($qr);
        return $this->_db->query();
    }

    public function emptyTrash(){
        $this->_db->setQuery("DELETE FROM mere WHERE MERE_DELF = 1 AND MERE_OCBA_CODE = '$this->_username'");
        $this->_db->query();

        return $this->_db->query();
    }

    public function getUserByUsername($username){
        $qr = "SELECT name FROM #__users WHERE username = '$username'";
       // echo $qr;
        $this->_db->setQuery($qr);

        $result = $this->_db->loadResult();
        return $result;
    }

    public function getUserByArrayUsername($arUsername){
        if(!$arUsername) return array();
        $username = null;
        foreach ($arUsername as $user) {
            $username[] = $user->MERE_OCBA_CODE;
        }
        $username = implode("','", $username);
        $qr = "SELECT id, name as MERE_OCBA_CODE FROM #__users WHERE username IN ('$username')";

        $this->_db->setQuery($qr);

        $result = $this->_db->loadObjectList();
        return $result;
    }


}
